package dao;

import db.JDBCUtil;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public boolean writeStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = ("insert into scoreOfStu(name,gender,birthday,mathScore,"
                        + "javaScore,sportScore )"
				+ "values(?,?,?,?,?,?)");
		int result = -1;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, student.getName());
			pstat.setString(2, student.getGender());
			pstat.setDate(3, new java.sql.Date(student.getBirthday()
							.getTime()));
                        pstat.setDouble(4, student.getScoreList().get(0).getScore());
                        pstat.setDouble(5, student.getScoreList().get(1).getScore());
                        pstat.setDouble(6, student.getScoreList().get(2).getScore());
			result = pstat.executeUpdate();
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstat, conn);
		}
		return result > 0 ? true : false;
	}

	@Override
	public Student readStudent(String name) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String sql = "select * from scoreOfStu where name like ?";
		Student student = null;
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt("id");
				String stuName = rs.getString("name");
				String stuGender = rs.getString("gender");
				Date stuDate = rs.getDate("birthday");
				Double stuScore1 = rs.getDouble("mathScore");
				Double stuScore2 = rs.getDouble("javaScore");
				Double stuScore3 = rs.getDouble("sportScore");
				student = new Student();
				student.getScoreList().get(0).setScore(stuScore1);
				student.getScoreList().get(1).setScore(stuScore2);
				student.getScoreList().get(2).setScore(stuScore3);
				student.setId(stuId);
				student.setGender(stuGender);
				student.setBirthday(stuDate);
				student.setName(stuName);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, pstat, conn);
		}
		return student;
	}

	@Override
	public List<Student> diplayAllStudent() {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		String sql = "select * from scoreOfStu";
		List<Student> list = new ArrayList<Student>();
		try {
			conn = JDBCUtil.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while (rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("stuno"));
				s.setName(rs.getString("name"));
				s.setGender(rs.getString("gender"));
				s.setBirthday(rs.getDate("birthday"));
                                double a=rs.getDouble("mathScore");
                                double b=rs.getDouble("javaScore");
                                double c=rs.getDouble("sportScore");
                                s.setTotalpoint(a+b+c);
                                s.setTotalpointavg((a+b+c)/3);
				s.getScoreList().get(0).setScore(rs.getDouble("mathScore"));
				s.getScoreList().get(1).setScore(rs.getDouble("javaScore"));
				s.getScoreList().get(2).setScore(rs.getDouble("sportScore"));
                                //System.out.println(s);
				list.add(s);
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, stat, conn);
		}
		return list;
	}

	@Override
	public void LearningReport(List<Student> list) {
		double majorscore1 = 0.0, majorscore2 = 0.0, majorscore3 = 0.0;
		for (int i = 0; i < list.size(); i++) {
			majorscore1 += list.get(i).getScoreList().get(0).getScore();
			majorscore2 += list.get(i).getScoreList().get(1).getScore();
			majorscore3 += list.get(i).getScoreList().get(2).getScore();
		}
                
		double[] majorpointavg = new double[3];
		majorpointavg[0] = majorscore1 / list.size();
		majorpointavg[1] = majorscore2 / list.size();
		majorpointavg[2] = majorscore3 / list.size();
                System.out.println("StudentList size()="+list.size());
                System.out.println("majorpointavg[0]="+majorpointavg[0]);
                System.out.println("majorpointavg[1]="+majorpointavg[1]);
                System.out.println("majorpointavg[2]="+majorpointavg[2]);
		double totalpointavg = 0.0;
		double totalpointall = 0.0;
		for (int i = 0; i < list.size(); i++) {
			list.get(i).setMajorpointavg(majorpointavg);
			double totalpoint = 0.0;
			totalpoint += list.get(i).getScoreList().get(0).getScore()
					+ list.get(i).getScoreList().get(1).getScore()
					+ list.get(i).getScoreList().get(2).getScore();
			list.get(i).setTotalpoint(totalpoint);
			totalpointall += totalpoint;
		}
		totalpointavg = totalpointall / list.size();
		for (int i = 0; i < list.size(); i++) {
			list.get(i).setTotalpointavg(totalpointavg);
		}
		Collections.sort(list, new Comparator<Student>() {
			@Override
			public int compare(Student o1, Student o2) {
				if (o1.getTotalpoint() > o1.getTotalpoint()) {
					return -1;
				}
				if (o1.getTotalpoint() < o1.getTotalpoint()) {
					return 1;
				}
				return 0;
			}

		});

		// 创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();
		// 建立新的sheet对象（excel的表单）
		HSSFSheet sheet = wb.createSheet("表");
		// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
		HSSFRow row1 = sheet.createRow(0);
		// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
		HSSFCell cell = row1.createCell(0);
		// 设置单元格内容
		cell.setCellValue("学生学习情况报表");
		// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
		// 在sheet里创建第二行
		HSSFRow row2 = sheet.createRow(1);
		// 创建单元格并设置单元格内容
		row2.createCell(0).setCellValue("学号");
		row2.createCell(1).setCellValue("姓名");
		row2.createCell(2).setCellValue("数学");
		row2.createCell(3).setCellValue("数学平均成绩");
		row2.createCell(4).setCellValue("Java");
		row2.createCell(5).setCellValue("Java平均成绩");
		row2.createCell(6).setCellValue("体育");
		row2.createCell(7).setCellValue("体育平均成绩");
		row2.createCell(8).setCellValue("总成绩");
		row2.createCell(9).setCellValue("班级总成绩平均值");
		for (int i = 0; i < list.size(); i++) {
			HSSFRow row = sheet.createRow((i + 2));
			row.createCell(0).setCellValue(list.get(i).getId());
			row.createCell(1).setCellValue(list.get(i).getName());
			row.createCell(2).setCellValue(
					list.get(i).getScoreList().get(0).getScore());
			row.createCell(3).setCellValue(list.get(i).getMajorpointavg(0));
			row.createCell(4).setCellValue(
					list.get(i).getScoreList().get(1).getScore());
			row.createCell(5).setCellValue(list.get(i).getMajorpointavg(1));
			row.createCell(6).setCellValue(
					list.get(i).getScoreList().get(2).getScore());
			row.createCell(7).setCellValue(list.get(i).getMajorpointavg(2));
			row.createCell(8).setCellValue(list.get(i).getTotalpoint());
			row.createCell(9).setCellValue(list.get(i).getTotalpointavg());
		}
		try {
			FileOutputStream fout = new FileOutputStream("D:/students.xls");
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Student findStudentById(int stuno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Student s = new Student();
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement("select name,stuno,mathScore，javaScore,sportScore from scoreOfStu where stuno='"
							+ stuno + "' ");
			rs = pstmt.executeQuery();
			System.out.println("---FindstudentBystuno---" + rs);
			while (rs.next()) {
				// 封装数据
				s.setId(stuno);
				s.setName(rs.getString("name"));
				s.setId(rs.getInt("stuno"));
				s.getScoreList().get(0).setScore(rs.getDouble("mathScore"));
				s.getScoreList().get(1).setScore(rs.getDouble("javaScore"));
				s.getScoreList().get(2).setScore(rs.getDouble("sportScore"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, pstmt, conn);
		}

		return s;
	}

	@Override
	public List<Student> findStudentByName(String name) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Student> list = new ArrayList<Student>();
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn
					.prepareStatement("select stuno,mathScore，javaScoreScore,sportScore from scoreOfStu where name like '%name%'");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("stuno"));
				s.setName(name);
				s.setGender(rs.getString("gender"));
				s.setBirthday(rs.getDate("birthday"));
				s.getScoreList().get(0).setScore(rs.getDouble("mathScore"));
				s.getScoreList().get(1).setScore(rs.getDouble("javaScore"));
				s.getScoreList().get(2).setScore(rs.getDouble("sportScore"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, pstmt, conn);
		}

		return list;
	}

	@Override
	public boolean delete(int stuno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int n = 0;
		String sql = "delete from scoreOfStu where stuno = ?";
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, stuno);
			n = pstmt.executeUpdate();
			System.out.println("删除语句执行结果-----" + n);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstmt, conn);
		}
		return n > 0 ? true : false;
	}

	@Override
	public boolean update(Student student) {
		// 拿到连接对象
		Connection conn = null;
		// 创建预处理命令对象
		PreparedStatement pstmt = null;
		int n = 0;
		try {
			conn = JDBCUtil.getConnection();
			pstmt = conn
					.prepareStatement("update scoreOfStu set name=?,gender=?,birthday=?,mathScore=?,Java=?,sportScore=?");
			pstmt.setString(1, student.getName());
			pstmt.setString(2, student.getGender());
			pstmt
					.setDate(3, new java.sql.Date(student.getBirthday()
							.getTime()));
			pstmt.setDouble(4, student.getScoreList().get(0).getScore());
			pstmt.setDouble(5, student.getScoreList().get(1).getScore());
			pstmt.setDouble(6, student.getScoreList().get(2).getScore());
			n = pstmt.executeUpdate();
			System.out.println("插入语句执行结果-----" + n);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(null, pstmt, conn);
		}
		return n > 0 ? true : false;
	}
}
